/*
 * SaleDAO.java
 *
 * Created on March 22, 2007, 1:45 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.group1E.data;

import edu.byu.isys413.group1E.Cache;
import edu.byu.isys413.group1E.ConnectionPool;
import edu.byu.isys413.group1E.ConnectionPoolException;
import edu.byu.isys413.group1E.DataException;
import edu.byu.isys413.group1E.GUID;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author tkulbeth
 */
public class SaleDAO extends RevenueSourceDAO{
    private static SaleDAO instance = null;
    
    /** Creates a new instance of SaleDAO */
    public SaleDAO() {
    }
    
    /**Retrieves an instance of the TransactionDAO object**/
    public static synchronized SaleDAO getInstance() {
        if (instance == null) {
            instance = new SaleDAO();
        }
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    /**
     * 
     * @throws java.lang.Exception 
     * @return 
     */
    public RevenueSourceBO create() throws Exception{
        String id = GUID.generate();
        SaleBO bo = new SaleBO(id);
        bo.setType("sale");
        Cache c = Cache.getInstance();
        c.put(bo.getRevSrcID(), bo);
        return bo;
    }
    
    /**
     * 
     * @param id 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.group1E.DataException 
     * @throws edu.byu.isys413.group1E.ConnectionPoolException 
     * @return 
     */
    public RevenueSourceBO read(String id) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        
        RevenueSourceBO sale = (SaleBO)c.get(id);
        if (sale != null){
            return sale;                                                        // if so, return it immediately
        }
        
        //otherwise, create a new sale object with the correct key, save it to the cache, and call super.read to fill in its data
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            sale = (SaleBO)this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id = " + id, e);
        }catch (ConnectionPoolException x) {
            //rollback
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return sale;
    }
    
    /**
     * 
     * @param id 
     * @param conn 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.group1E.DataException 
     * @throws edu.byu.isys413.group1E.ConnectionPoolException 
     * @return 
     */
    public synchronized RevenueSourceBO read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        SaleBO sale = (SaleBO)c.get(id);
        if (sale != null){
            return sale;                                                        // if so, return it immediately
        }
        //put it to the cache so the super.read can get it
        sale = new SaleBO(id);        
        c.put(id, sale);
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        
        sale = (SaleBO)super.read(id, conn);
        
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Sale WHERE RevenueSourceID = '" + id + "'");
        conn.commit();
        
        String productID;
        //build the object from the result set
        if (rs.next()) {
            sale.setQuantity(rs.getInt("Quantity"));
            productID = rs.getString("productid");
            
            
            int temp;
            
            temp = rs.getInt("returnline");
            if(temp == 0){
                sale.setReturnLine(false);
            }
            else{
                sale.setReturnLine(true);
            }
            
            ResultSet rs2 = stmt.executeQuery("SELECT * FROM PRODUCT WHERE ProductID = '" + productID + "'");
            
            if(rs2.next()){
                String prodType = rs2.getString("prodtype");
                if(prodType.equals("physical")){
                    ForSaleDAO forSaleDAO = ForSaleDAO.getInstance();
                    sale.setProduct(forSaleDAO.read(productID));                
                }
                if(prodType.equals("conceptual")){
                    ConceptualDAO conceptualDAO = ConceptualDAO.getInstance();
                    sale.setProduct(conceptualDAO.read(productID));
                }                
            }//end rs2  
            
            sale.setAlreadyInDB(true);
            sale.setIsDirty(false);
            
            // save to the cache
            c.put(sale.getRevSrcID(), sale);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        
         
        
        stmt.close();
        // return the BO
        return sale;
    }
    
    /**
     * 
     * @throws edu.byu.isys413.group1E.DataException 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.group1E.ConnectionPoolException 
     * @return 
     */
    public synchronized ArrayList<RevenueSourceBO> readAll() throws DataException, SQLException, ConnectionPoolException{
//        ProductDAO prodDAO = ProductDAO.getInstance();
        ArrayList<RevenueSourceBO> list = new ArrayList<RevenueSourceBO>();
//now build the query
        String query = "SELECT * FROM Sale";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        Cache c = Cache.getInstance();
        
        
//extract the elements from the result set and build a list of results
//build the object from the result set
        while (rs.next()) {
            SaleBO sale = new SaleBO(rs.getString("RevenueSourceID"));
            c.put(sale.getRevSrcID(), sale);            
            
            sale = (SaleBO)super.read(rs.getString("RevenueSourceID"), conn);
            sale.setQuantity(rs.getInt("quantity"));
            
            int temp;
            
            temp = rs.getInt("returnline");
            if(temp == 0){
                sale.setReturnLine(false);
            }
            else{
                sale.setReturnLine(true);
            }            
            
            ProductDAO productDAO = ProductDAO.getInstance();            
            sale.setProduct(productDAO.read(rs.getString("productid")));
            Cache.getInstance().put(rs.getString("revenueSourceID"), sale);
            sale = (SaleBO)super.read(rs.getString("RevenueSourceID"), conn);
            
            sale.setQuantity(rs.getInt("quantity"));
//            sale.setProduct(ProductDAO.read(rs.getString("productID")));
            sale.setAlreadyInDB(true);
            sale.setIsDirty(false);
            
// save to the list
            list.add(sale);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return list;
    }
    
    /**
     * 
     * @param bo 
     * @param conn 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.group1E.DataException 
     */
    protected void update(RevenueSourceBO bo, Connection conn) throws SQLException, DataException {
        //update the DB
        SaleBO BO = (SaleBO)bo;
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Sale SET quantity = ?, productID = ?, returnline = ? WHERE revenueSourceID = ?");
        
        pstmt.setInt(1, BO.getQuantity());
        pstmt.setString(2, BO.getProduct().getProductID());
        
        int temp;
        if(BO.getReturnLine() == true){
            temp = 1;
        }else{
            temp = 0;
        }        
        pstmt.setInt(3, temp);
        
        pstmt.setString(4, BO.getRevSrcID());
        pstmt.executeUpdate();
        pstmt.close();
        BO.setAlreadyInDB(true);
    }// end update
    
    synchronized void insert(RevenueSourceBO bo, Connection conn) throws SQLException, DataException {
//update the DB
        SaleBO BO = (SaleBO)bo;
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Sale (revenueSourceID, quantity, productid, returnline)" +
                "VALUES (?, ?, ?, ?)");
        pstmt.setString(1, BO.getRevSrcID());
        pstmt.setInt(2, BO.getQuantity());
        pstmt.setString(3, BO.getProduct().getProductID());
        
        int temp;
        if(BO.getReturnLine() == true){
            temp = 1;
        }else{
            temp = 0;
        }        
        pstmt.setInt(4, temp);
        
        pstmt.executeUpdate();
        pstmt.close();
// tell the object that it's now in the db (so we call update next time not insert)
        BO.setAlreadyInDB(true);
    }//end insert
    
    
    /** This method enables a user to search for a sale by a product ID. This would return a specific sale 
     *if the product is a physical product, but an array of products if the id is for a conceptual product.
     *@param productID contains the product id to be searched by
     *@return an array of SaleBOs that match the product ID criteria.
     */
    public synchronized ArrayList<RevenueSourceBO> readByProductID(String productID) throws SQLException, DataException, ConnectionPoolException{
        ArrayList<RevenueSourceBO> list = new ArrayList<RevenueSourceBO>();
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Sale WHERE ProductID = '" + productID + "'");
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        //build the object from the result set and add it to the list.  (should only return one if the
        //productID was for a physical product
        while (rs.next()) {
            SaleBO sale = new SaleBO(rs.getString("RevenueSourceID"));
            Cache.getInstance().put(rs.getString("RevenueSourceID"), sale);
            sale = (SaleBO)super.read(rs.getString("RevenueSourceID"), conn);
            sale.setQuantity(rs.getInt("quantity"));
            list.add(sale);
        }
        ConnectionPool.getInstance().release(conn);
        return list;
    }
}
